package com.sl.au.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.EventRegister;
import com.sl.au.entity.EventRegisterAndTaskView;
import com.sl.au.entity.EventReport;
import com.sl.au.entity.EventTask;
import com.sl.au.entity.Option;

public interface EventRegisterRepository extends JpaRepository<EventRegister, String> {
	/**
	 * 快速查询
	 * 
	 * @param key
	 * @return
	 */
	@Query("select a from EventRegister a where a.registerEventName like %?1% or a.registerEventContent like %?1% or a.registerEventState.title like %?1%")
	Page<EventRegister> getByKey(String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1")
	Page<EventRegister> initUnArchievedEventReg(String optionId, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1")
	Page<EventRegister> initPageByStateId(String optionId, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1 and (e.registerEventName like %?2% or e.registerEventContent like %?2% or e.registerEventState.title like %?2%)")
	Page<EventRegister> findByStateWithKey(String state, String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1 and e.registerEventCreateUser.id=?2 and (e.registerEventName like %?3% or e.registerEventContent like %?3% or e.registerEventState.title like %?3%)")
	Page<EventRegister> findByStateAndUserWithKey(String state, String user, String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1 and e.registerEventCreateUser.id=?2")
	Page<EventRegister> findByStateAndUser(String state, String user, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventCreateUser.id=?1 and (e.registerEventName like %?2% or e.registerEventContent like %?2% or e.registerEventState.title like %?2%)")
	Page<EventRegister> findByUserWithKey(String user, String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventCreateUser.id=?1")
	Page<EventRegister> findByUser(String user, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToReportEvent.id=?1")
	List<EventRegister> findByReportId(String reportId);

	@Query("select e from EventRegister e where e.registerEventToReportEvent.id=?1")
	Page<EventRegister> findByEventReportId(String reportId, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToReportEvent.id=?1 and (e.registerEventName like %?2% or e.registerEventContent like %?2% or e.registerEventState.title like %?2%)")
	Page<EventRegister> findByEventReportIdWithKey(String reportId, String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToReportEvent.id=?1")
	Page<EventRegister> getRegistersPageByReportId(String reportId, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToNetGrid.id in (select v.grid_id from ViewForUserGridInfo v where v.user_id=?1) and e.registerEventType in(select u.eventType from UserEventTypeBindInfo u where u.user.id=?1)")
	Page<EventRegister> findByUserGrid(String user, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToNetGrid.id in (select v.grid_id from ViewForUserGridInfo v where v.user_id=?1) and e.registerEventType in(select u.eventType from UserEventTypeBindInfo u where u.user.id=?1) and (e.registerEventName like %?2% or e.registerEventContent like %?2% or e.registerEventState.title like %?2%)")
	Page<EventRegister> findByUserGridWithKey(String user, String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1 and e.registerEventToNetGrid.id in (select v.grid_id from ViewForUserGridInfo v where v.user_id=?2) and e.registerEventType in(select u.eventType from UserEventTypeBindInfo u where u.user.id=?2) and (e.registerEventName like %?3% or e.registerEventContent like %?3% or e.registerEventState.title like %?3%)")
	Page<EventRegister> findByStateAndUserGridWithKey(String state, String user, String key, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventState.id=?1 and e.registerEventToNetGrid.id in (select v.grid_id from ViewForUserGridInfo v where v.user_id=?2) and e.registerEventType in(select u.eventType from UserEventTypeBindInfo u where u.user.id=?2)")
	Page<EventRegister> findByStateAndUserGrid(String state, String user, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToReportEvent.id=?1 and (e.registerEventName like %?2% or e.registerEventContent like %?2% or e.registerEventState.title like %?2%) and e.registerEventState.title like %?3")
	Page<EventRegister> findByEventReportIdWithKeyAndType(String reportId, String key, String type, Pageable pageable);

	@Query("select e from EventRegister e where e.registerEventToReportEvent.id=?1 and e.registerEventState.title like %?2")
	Page<EventRegister> findByEventReportIdAndType(String reportId, String type, Pageable pageable);

	@Query("select a from EventRegister a where (a.registerEventName like %?1% or a.registerEventContent like %?1% or a.registerEventState.title like %?1%) and a.registerEventState.title like %?2")
	Page<EventRegister> getByKeyAndType(String key, String type, Pageable pageable);

	@Query("select a from EventRegister a where a.registerEventState.title like %?1")
	Page<EventRegister> getByType(String type, Pageable pageable);

	@Query("select count(e) from EventRegister e , EventTask t where e.id=t.eventId and t.eventTaskUser.id=?1 and t.eventTaskState.id=?2")
	int getRegisterCountByUserAndType(String userId, String type);

	@Query("select e from EventRegister e , EventTask t where e.id=t.eventId and t.eventTaskUser.id=?1 and t.eventTaskState.id=?2")
	Page<EventRegister> getRegisterUserAndType(String userId, String type, Pageable pageable);

	// @Query("select distinct new
	// com.sl.au.entity.EventRegisterAndTaskView(e,b) from EventRegister
	// e,EventTask b where e.id=b.eventId and
	// e.registerEventState=b.eventTaskState and
	// e.registerEventToReportEvent.id=?1 and (e.registerEventName like %?2% or
	// e.registerEventContent like %?2% or e.registerEventState.title like %?2%)
	// and e.registerEventState.title like %?3")
	// Page<EventRegisterAndTaskView> findPageByReportWithKeyAndType(String
	// eventReport, String key, String type,
	// Pageable pageable);
	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(e,b) from EventRegister e,ViewForTimeOutTask b where b.unfinished = '1' and e.id=b.eventId and e.registerEventState=b.eventTaskState and e.registerEventToReportEvent.id=?1 and (e.registerEventName like %?2% or e.registerEventContent like %?2% or e.registerEventState.title like %?2%) and e.registerEventState.title like %?3")
	Page<EventRegisterAndTaskView> findPageByReportWithKeyAndType(String eventReport, String key, String type,
			Pageable pageable);

	// @Query("select distinct new
	// com.sl.au.entity.EventRegisterAndTaskView(e,b) from EventRegister
	// e,EventTask b where e.id=b.eventId and
	// e.registerEventState=b.eventTaskState and
	// e.registerEventToReportEvent.id=?1 and e.registerEventState.title like
	// %?2")
	// Page<EventRegisterAndTaskView> findPageByReportAndType(String
	// eventReport, String type, Pageable pageable);
	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(e,b) from EventRegister e,ViewForTimeOutTask b where b.unfinished = '1' and e.id=b.eventId and e.registerEventState=b.eventTaskState and e.registerEventToReportEvent.id=?1 and e.registerEventState.title like %?2")
	Page<EventRegisterAndTaskView> findPageByReportAndType(String eventReport, String type, Pageable pageable);

	// @Query("select distinct new
	// com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister
	// a,EventTask b where a.id=b.eventId and
	// a.registerEventState=b.eventTaskState and (a.registerEventName like %?1%
	// or a.registerEventContent like %?1% or a.registerEventState.title like
	// %?1%) and a.registerEventState.title like %?2")
	// Page<EventRegisterAndTaskView> findByKeyAndType(String key, String type,
	// Pageable pageable);
	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister a,ViewForTimeOutTask b where b.unfinished = '1' and a.id=b.eventId and a.registerEventState=b.eventTaskState and (a.registerEventName like %?1% or a.registerEventContent like %?1% or a.registerEventState.title like %?1%) and a.registerEventState.title like %?2")
	Page<EventRegisterAndTaskView> findByKeyAndType(String key, String type, Pageable pageable);

	// @Query("select distinct new
	// com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister
	// a,EventTask b where a.id=b.eventId and
	// a.registerEventState=b.eventTaskState and a.registerEventState.title like
	// %?1")
	// Page<EventRegisterAndTaskView> findByType(String type, Pageable
	// pageable);
	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister a, ViewForTimeOutTask b where b.unfinished = '1' and a.id = b.eventId and a.registerEventState = b.eventTaskState and a.registerEventState.title like %?1")
	Page<EventRegisterAndTaskView> findByType(String type, Pageable pageable);

	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister a,EventTask b where a.id=b.eventId and a.registerEventState=b.eventTaskState and a.registerEventState=?2 and (TIME_TO_SEC(TIMEDIFF(now(),b.eventTaskTime))>a.dealLimitSpan*2*3600/3 and TIME_TO_SEC(TIMEDIFF(now(),b.eventTaskTime))<a.dealLimitSpan*3600) and (a.registerEventName like %?1% or a.registerEventContent like %?1% or a.registerEventState.title like %?1%)")
	Page<EventRegisterAndTaskView> findUnderTimeoutEventByKey(Pageable pageable, String key, Option type);

	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister a,EventTask b where a.id=b.eventId and a.registerEventState=b.eventTaskState and a.registerEventState=?1 and (TIME_TO_SEC(TIMEDIFF(now(),b.eventTaskTime))>a.dealLimitSpan*2*3600/3 and TIME_TO_SEC(TIMEDIFF(now(),b.eventTaskTime))<a.dealLimitSpan*3600)")
	Page<EventRegisterAndTaskView> findUnderTimeoutEvent(Pageable pageable, Option type);

	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister a, ViewForTimeOutTask b where b.unfinished = '1' and a.id=b.eventId and a.registerEventState=b.eventTaskState and a.registerEventState=?2 and (a.dealLimitSpan - b.diffTime) > 0 and (a.dealLimitSpan - b.diffTime) <= ?3 and (a.registerEventName like %?1% or a.registerEventContent like %?1% or a.registerEventState.title like %?1%)")
	Page<EventRegisterAndTaskView> findUnderTimeoutEventByKey(String key, Option type, double limit, Pageable pageable);

	@Query("select count(1) from EventRegister a, ViewForTimeOutTask b where b.unfinished = '1' and a.id=b.eventId and a.registerEventState=b.eventTaskState and a.registerEventState=?2 and (a.dealLimitSpan - b.diffTime) > 0 and (a.dealLimitSpan - b.diffTime) <= ?3 and (a.registerEventName like %?1% or a.registerEventContent like %?1% or a.registerEventState.title like %?1%)")
	int getCountUnderTimeoutEventByKey(String key, Option type, double limit);

	@Query("select distinct new com.sl.au.entity.EventRegisterAndTaskView(a,b) from EventRegister a, ViewForTimeOutTask b where b.unfinished = '1' and a.id=b.eventId and a.registerEventState=b.eventTaskState and a.registerEventState=?1 and (a.dealLimitSpan - b.diffTime) > 0 and (a.dealLimitSpan - b.diffTime) <= ?2")
	Page<EventRegisterAndTaskView> findUnderTimeoutEvent(Option type, double limit, Pageable pageable);

	@Query("select count(1) from EventRegister a, ViewForTimeOutTask b where b.unfinished = '1' and a.id=b.eventId and a.registerEventState=b.eventTaskState and a.registerEventState=?1 and (a.dealLimitSpan - b.diffTime) > 0 and (a.dealLimitSpan - b.diffTime) <= ?2")
	int getCountUnderTimeoutEvent(Option type, double limit);

	@Query("select e from EventTask e where e.eventId=?1 and e.eventTaskState=?2")
	EventTask findTask(String registerId, Option registerEventState);
	
	@Query("select a from EventRegister a where a.registerEventAccTime >= ?1 and a.registerEventAccTime <= ?2")
	Page<EventRegister> getPageByEventDate(Date begin, Date end, Pageable pageable);

	@Query("select count(a) from EventRegister a where a.registerEventAccTime >= ?1 and a.registerEventAccTime <= ?2")
	int getPageCountByEventDate(Date begin, Date end);
}
